﻿// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

// THIS CODE HAS BEEN MODIFIED FROM SHARPMAP PROJECT
// Copyright 2005, 2006 - Morten Nielsen (www.iter.dk)
// SharpMap is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.

using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Security.Principal;
using System.ComponentModel;
using Microsoft.SqlServer.Server;
using MsSqlSpatial;
using GeoAPI.Geometries;
using GisSharpBlog.NetTopologySuite.IO;
using GisSharpBlog.NetTopologySuite.Geometries;

public partial class StoredProcedures
{
	[SqlProcedure]
	public static void ImportFromShapefile(
		SqlString shapefile,
		[DefaultValue("")] SqlString schema_name,
		[DefaultValue("")] SqlString table_name,
		[DefaultValue("")] SqlString column_name,
		[DefaultValue(-1)] SqlInt32 srid,
		[DefaultValue("GEOMETRY")] SqlString type,
		[DefaultValue(false)] SqlBoolean appendRows
		)
	{
		long importedRows;
		TimeSpan elapsedTime;
		
		// TODO: Impersonation implementation pending. I'm getting the following error:
		// Msg 10312, Level 16, State 49, Procedure ImportFromShapefile, Line 0
		// .NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.
		
//		WindowsImpersonationContext originalContext = null;
//		WindowsIdentity callerIdentity = SqlContext.WindowsIdentity;
//		
//		if (callerIdentity != null) 
//		{
//			originalContext = callerIdentity.Impersonate();
		ImportFromShapefileMain(shapefile.Value, schema_name.Value, table_name.Value, column_name.Value, srid.Value, type.Value, "context connection=true", appendRows.IsTrue, out importedRows, out elapsedTime);
			//		}
//		if (originalContext != null)
//			originalContext.Undo();

		SqlContext.Pipe.Send(string.Format("{0} records imported in {1}.", importedRows, FormatElapsedTime(elapsedTime)));

	}

	public static string FormatElapsedTime(TimeSpan elapsedTime)
	{
		if (elapsedTime.Minutes < 1)
			return string.Format("{0} seconds", elapsedTime.Seconds);
		else if (elapsedTime.Hours < 1)
			return string.Format("{0} minutes", elapsedTime.TotalMinutes.ToString("N"));
		else
			return elapsedTime.ToString();
	}

	public static void ImportFromShapefileMain(
		string shapefile,
		string schema_name,
		string table_name,
		string column_name,
		int srid,
		string type,
		string connectionString,
		bool appendRows,
		out long importedRows,
		out TimeSpan elapsedTime
	)
	{
		// Default table name to shapefile name.
		string filename = Path.GetFileName(shapefile);
		if (string.IsNullOrEmpty(table_name))
			table_name = filename.Remove(filename.Length - 4, 4); // remove .shp

		if (string.IsNullOrEmpty(column_name))
			column_name = "the_geom";

		using (IDataImportProcess process = new ShapefileImportProcess(shapefile))
			DataImportProcess(process, schema_name, table_name, column_name, srid, type, connectionString, appendRows, out importedRows, out elapsedTime);

	}

	public interface IDataImportProcess : IDisposable
	{
		Dictionary<string, Type> GetColumns();
		void Execute(SqlCommand command, string columnName, int srid, out long importedRows);
	}

	public class ShapefileImportProcess : IDataImportProcess
	{
		ShapefileDataReader reader;
		Dictionary<string, Type> columns;

		public ShapefileImportProcess(string filename)
		{
			reader = new ShapefileDataReader(filename, GeometryFactory.Default);
		}

		public Dictionary<string, Type> GetColumns()
		{
			if (columns == null)
			{
				columns = new Dictionary<string, Type>();
				foreach (DbaseFieldDescriptor col in reader.DbaseHeader.Fields)
				{
					columns[col.Name] = col.Type;
				}
			}
			return columns;
		}

		public void Execute(SqlCommand command, string columnName, int srid, out long importedRows)
		{
			importedRows = 0;
			Dictionary<string, Type> columns = GetColumns();

			while (reader.Read())
			{
				int i = 0;
				foreach (string column in columns.Keys)
				{
					command.Parameters["@" + column].Value = reader.GetValue(i);
					i++;
				}
				command.Parameters["@" + columnName].Value = ConvertGeometry.ToSqlBytes(reader.Geometry, srid).Value;

				command.ExecuteNonQuery();
				importedRows++;
			}
		}

		public void Dispose()
		{
			reader.Dispose();
		}
	}

	public static void DataImportProcess(
		IDataImportProcess process,
		string schemaName,
		string tableName,
		string columnName,
		int srid,
		string type,
		string connectionString,
		bool appendRows,
		out long importedRows,
		out TimeSpan elapsedTime
		)
	{
		importedRows = -1;
		elapsedTime = TimeSpan.Zero;

		DateTime start = DateTime.Now;

		DatabaseHelper.ContextConnectionString = connectionString;

		if (!SpatialRefSysTable.IsValidSRID(srid))
		{
			throw new InvalidSridException(srid);
		}

		using (SqlConnection connection = DatabaseHelper.ContextConnection)
		{
			connection.Open();
			using (SqlTransaction transaction = connection.BeginTransaction())
			{
				using (SqlCommand command = connection.CreateCommand())
				{
					command.Transaction = transaction;
					command.CommandTimeout = 0;
					ExecuteCreateMetaTables(command);
					schemaName = DatabaseHelper.CheckSchemaName(command, schemaName);
					Dictionary<string, Type> columns = process.GetColumns();

					if (appendRows)
					{
						GeometryTable.ExecuteDropIndexes(command, schemaName, tableName, columnName);
						GeometryTable.ExecuteDropConstraintSRID(command, schemaName, tableName, columnName);
						GeometryTable.ExecuteDropConstraintGeometryType(command, schemaName, tableName, columnName);
						GeometryTable.ExecuteDropTrigger(command, schemaName, tableName, columnName);
					}
					else
					{
						//Try to drop table if it exists
						try
						{
							DatabaseHelper.ExecuteDropTable(command, schemaName, tableName);
						}
						catch { }

						//Create new table for storing the datasource
						string createColumns = "";
						foreach (KeyValuePair<string, Type> col in columns)
						{
							if (col.Value != typeof(String))
								createColumns += ", " + col.Key + " " + Type2SqlType(col.Value).ToString();
							else
								createColumns += ", " + col.Key + " VARCHAR(4000)";
						}
						GeometryTable.ExecuteCreate(command, schemaName, tableName, columnName, createColumns);
					}

					CreateInsertCommand(command, schemaName, tableName, columnName, columns);
					process.Execute(command, columnName, srid, out importedRows);
					command.Parameters.Clear();

					// Create indexes, meta-info and constraints
					if (!appendRows)
					{
						GeometryColumnsTable.ExecuteAddMetaRecord(command, schemaName, tableName, columnName, srid, type);
						ExecuteAddGeometryQueryFunctions(command, schemaName, tableName, columnName);
					}
					GeometryTable.ExecuteAddIndexes(command, schemaName, tableName, columnName);
					GeometryTable.ExecuteAddConstraintSRID(command, schemaName, tableName, columnName, srid);
					GeometryTable.ExecuteAddConstraintGeometryType(command, schemaName, tableName, columnName, type);
					GeometryTable.ExecuteCreateTrigger(command, schemaName, tableName, columnName);

					transaction.Commit();
				}
			}
		}

		elapsedTime = DateTime.Now - start;
	}

	private static void CreateInsertCommand(SqlCommand command, string schemaName, string tableName, string geometryColumnName, Dictionary<string, Type> columns)
	{
		//Create insert script
		string strSQL = " (";
		foreach (KeyValuePair<string, Type> col in columns)
			strSQL += "@" + col.Key + ",";

		strSQL += "@" + geometryColumnName + ")";
		strSQL = "INSERT INTO [" + schemaName + "].[" + tableName + "] " + strSQL.Replace("@", "") + " VALUES" + strSQL;

		DatabaseHelper.BuildCommand(command, strSQL);
		//Add feature columns parameters
		foreach (KeyValuePair<string, Type> col in columns)
			if (col.Value == typeof(string))
				command.Parameters.Add("@" + col.Key, SqlDbType.VarChar, 4000);
			else
				command.Parameters.Add("@" + col.Key, Type2SqlType(col.Value));

		//Add geometry column parameter
		command.Parameters.Add("@" + geometryColumnName, SqlDbType.VarBinary, int.MaxValue);
		
		command.Prepare();
	}

	/// <summary>
	/// Returns the name of the SqlServer datatype based on a .NET datatype
	/// </summary>
	/// <param name="t"></param>
	/// <returns></returns>
	private static SqlDbType Type2SqlType(Type t)
	{
		switch (t.ToString())
		{
			case "System.Boolean": return System.Data.SqlDbType.Bit;
			case "System.Single": return System.Data.SqlDbType.Real;
			case "System.Double": return System.Data.SqlDbType.Float;
			case "System.Int16": return System.Data.SqlDbType.SmallInt;
			case "System.Int32": return System.Data.SqlDbType.Int;
			case "System.Int64": return System.Data.SqlDbType.BigInt;
			case "System.DateTime": return System.Data.SqlDbType.DateTime;
			case "System.Byte[]": return System.Data.SqlDbType.Image;
			case "System.String": return System.Data.SqlDbType.VarChar;
			default:
				throw (new NotSupportedException("Unsupported datatype '" + t.Name + "' found in datasource"));
		}
	}
};
